clear all
cap close
set more off

cd ".\Data_Program"

* This file matches counties with their neighbors' fips code and neighbors' fintech penetration

* I. Distance data
use Econdata\sf12010countydistancemiles

gen st1=floor(county1/1000)
gen st2=floor(county2/1000)
drop if inlist(st1,2,15)|st1>56
drop if inlist(st2,2,15)|st2>56

keep if mi<=200

sort county1 mi
by county1: gen nearest=_n
keep if nearest<=10
drop mi

expand 44   //2007-2017

sort county1 county2
by county1 county2: gen quarter=q(2007q1)+_n-1
format quarter %tq


* II. Merge with county-level finshare 
rename county2 county
merge m:1 county quarter using county
keep if _merge==3 
keep quarter county1 county nearest finshare 
rename finshare finshare2

* county finshare_ad
merge m:1 county quarter using Proprietary\mintel
drop if _merge==2 
drop _merge
replace finad=0 if finad==.
replace nonfinad=0 if nonfinad==.
replace ad=0 if ad==.
drop ad nonfinad
rename finad finad2

    * CoreLogic house price growth
merge m:1 county quarter using Proprietary\hpi_county_quarter_exp
drop if _merge==2 
replace hpgr=hpgr4
drop _merge qhpi hpgr1 hpgr4
rename hpgr hpgr2


    * Census population growth
gen year=yofd(dofq(quarter))
merge m:1 year county using Econdata\pop_county
drop if _merge==2  
drop _merge fracm frach pop
rename popgr popgr2 
rename fracy fracy2
rename fracb fracb2


    * Census employment growth
merge m:1 year county using Econdata\emp_county_year
drop if _merge==2  
drop _merge year
rename empgr empgr2

    * Equifax CCP subprime share
merge m:1 quarter county using Proprietary\subprime_county_quarter
drop if _merge==2 
drop _merge 
rename subprime subprime2

     * Census education level in 2010
merge m:1 county using Econdata\edu
drop if _merge==2  
drop _merge hsabove
rename babove babove2

* IV. Reshape to wide
sort quarter county1 nearest 

drop county

reshape wide *2, i(county1 quarter) j(nearest)

rename county1 county

xtset county quarter   
save distance_10nb.dta,replace